const mysql = require('mysql2/promise');
const bcrypt = require('bcryptjs');
require('dotenv').config();

const pool = mysql.createPool({
    host: process.env.DB_HOST || 'localhost',
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '123456',
    database: process.env.DB_NAME || 'web前端',
    waitForConnections: true,
    connectionLimit: 10,
    queueLimit: 0
});

// 初始化数据库表
async function initDatabase() {
    try {
        const connection = await pool.getConnection();
        console.log('数据库连接成功');

        // 创建用户表
        await connection.query(`
            CREATE TABLE IF NOT EXISTS users (
                id INT AUTO_INCREMENT PRIMARY KEY,
                username VARCHAR(255) UNIQUE NOT NULL,
                password VARCHAR(255) NOT NULL,
                email VARCHAR(255),
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
            )
        `);
        console.log('用户表创建成功');

        // 创建设备表
        await connection.query(`
            CREATE TABLE IF NOT EXISTS devices (
                id INT AUTO_INCREMENT PRIMARY KEY,
                user_id INT NOT NULL,
                name VARCHAR(255) NOT NULL,
                type VARCHAR(50) NOT NULL,
                location VARCHAR(255) NOT NULL,
                status BOOLEAN DEFAULT FALSE,
                created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
                updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
                FOREIGN KEY (user_id) REFERENCES users(id)
            )
        `);
        console.log('设备表创建成功');

        // 检查是否存在测试用户
        const [users] = await connection.query('SELECT id FROM users WHERE username = ?', ['test']);

        if (users.length === 0) {
            const hashedPassword = await bcrypt.hash('123456', 10);

            // 创建测试用户
            const [result] = await connection.query(
                'INSERT INTO users (username, password, email) VALUES (?, ?, ?)',
                ['test', hashedPassword, 'test@example.com']
            );
            console.log('测试用户创建成功');

            // 为测试用户添加示例设备
            const devices = [
                { name: '客厅灯', type: '照明', location: '客厅', status: true },
                { name: '主卧空调', type: '空调', location: '主卧', status: true },
                { name: '厨房电饭煲', type: '厨具', location: '厨房', status: false }
            ];

            for (const device of devices) {
                await connection.query(
                    'INSERT INTO devices (user_id, name, type, location, status) VALUES (?, ?, ?, ?, ?)',
                    [result.insertId, device.name, device.type, device.location, device.status]
                );
            }
            console.log('示例设备创建成功');
        }

        connection.release();
        console.log('数据库初始化完成');
    } catch (error) {
        console.error('数据库初始化失败:', error);
        throw error;
    }
}

// 初始化数据库
initDatabase().catch(console.error);

module.exports = pool; 